﻿using System;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using Microsoft.SharePoint;

namespace FastPMOSite.ControlTemplates.FastPMOSite
{
    public partial class FastProjectAdmin : UserControl
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            UpdateProjectData();
        }

        private void UpdateProjectData()
        {
            try
            {

                string sqlCon = System.Configuration.ConfigurationManager.ConnectionStrings["FastSharePoint"].ConnectionString;

                System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(sqlCon);

                con.Open();

                System.Data.SqlClient.SqlCommand cmd1 = new System.Data.SqlClient.SqlCommand("GetProjectsCount", con);
                cmd1.CommandType = System.Data.CommandType.StoredProcedure;
                ProjectLiteral.Text = "Projects - " + Convert.ToString((int)cmd1.ExecuteScalar()) + "<br>";

                System.Data.SqlClient.SqlCommand cmd2 = new System.Data.SqlClient.SqlCommand("GetUsersCount", con);
                cmd2.CommandType = System.Data.CommandType.StoredProcedure;
                ProjectLiteral.Text += "Users - " + Convert.ToString((int)cmd2.ExecuteScalar()) + "<br>";

                System.Data.SqlClient.SqlCommand cmd3 = new System.Data.SqlClient.SqlCommand("GetDeliverablesCount", con);
                cmd3.CommandType = System.Data.CommandType.StoredProcedure;
                ProjectLiteral.Text += "Deliverables - " + Convert.ToString((int)cmd3.ExecuteScalar()) + "<br>";

                System.Data.SqlClient.SqlCommand cmd4 = new System.Data.SqlClient.SqlCommand("GetTasksCount", con);
                cmd4.CommandType = System.Data.CommandType.StoredProcedure;
                ProjectLiteral.Text += "Tasks - " + Convert.ToString((int)cmd4.ExecuteScalar()) + "<br>";

                System.Data.SqlClient.SqlCommand cmd5 = new System.Data.SqlClient.SqlCommand("GetIssuesCount", con);
                cmd5.CommandType = System.Data.CommandType.StoredProcedure;
                ProjectLiteral.Text += "Issues - " + Convert.ToString((int)cmd5.ExecuteScalar()) + "<br>";

                System.Data.SqlClient.SqlCommand cmd6 = new System.Data.SqlClient.SqlCommand("GetRisksCount", con);
                cmd6.CommandType = System.Data.CommandType.StoredProcedure;
                ProjectLiteral.Text += "Risks - " + Convert.ToString((int)cmd6.ExecuteScalar());
                con.Close();
            }
            catch (Exception ex)
            {
                Response.Write(ex.Message);
            }
        }

        protected void btnUpdate_Click(object sender, EventArgs e)
        {
            string sqlCon = System.Configuration.ConfigurationManager.ConnectionStrings["FastSharePoint"].ConnectionString;

            System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(sqlCon);

            SPWeb web = SPContext.Current.Web;

            SPList projects = web.Lists["Projects"];

            con.Open();

            System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("TruncateListTables", con);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.ExecuteNonQuery();

            foreach (SPListItem item in projects.Items)
            {
                SPWeb projectWeb = SPContext.Current.Site.OpenWeb("/" + (string)item["ProjectSiteUrl"]);

                SPList deliverables = projectWeb.Lists["Deliverables"];
                AddListItems(projectWeb, deliverables, "InsertDeliverable", con);

                SPList tasks = projectWeb.Lists["Tasks"];
                AddListItems(projectWeb, tasks, "InsertTask", con);

                SPList issues = projectWeb.Lists["Issues"];
                AddListItems(projectWeb, issues, "InsertIssue", con);

                SPList risks = projectWeb.Lists["Risks"];
                AddListItems(projectWeb, risks, "InsertRisk", con);

            }

            con.Close();

            UpdateProjectData();
        }

        private void AddListItems(SPWeb web, SPList list, string cmdName, System.Data.SqlClient.SqlConnection con)
        {
            foreach (SPListItem item in list.Items)
            {
                try
                {
                    System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(cmdName, con);

                    cmd.CommandType = System.Data.CommandType.StoredProcedure;

                    string assignedTo = item["Assigned To"].ToString();

                    cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@WebID", web.ID));
                    cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@WebName", web.Name));
                    cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@UserID", assignedTo.Substring(0, assignedTo.IndexOf(";#"))));
                    cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@UserName", assignedTo.Substring(assignedTo.IndexOf(";#") + 2)));
                    cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ListID", list.ID));
                    cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ListName", list.Title));
                    cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ItemID", item.ID));
                    cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ItemName", item.Title));
                    cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@StartDate", item["Start Date"]));
                    cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@EndDate", item["Due Date"]));
                    cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Status", item["Task Status"]));

                    cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    Response.Write(ex.Message);
                }
            }

        }

    }
}
